# import os
# if os.getenv('AG_ENV_PYTHON_PATH') is None:
#     ROOT_DIR = os.path.dirname(os.path.abspath(__file__)) + os.sep
# else:
#     ROOT_DIR = os.getcwd() + os.sep
#     # ROOT_DIR = WORK_DIR + "ag_art" + os.sep
# parent_dir = os.path.dirname(ROOT_DIR)
# import importlib.util
# abstractjob_path = parent_dir+'/SKO/AbstractDPJob.py'
# spec = importlib.util.spec_from_file_location('AbstractDPJob', abstractjob_path)
# abstractjob_module = importlib.util.module_from_spec(spec)
# spec.loader.exec_module(abstractjob_module)
# AbstractDPJob = abstractjob_module.AbstractDPJob

from SKO.AbstractDPJob import AbstractDPJob
import sys, datetime, json
import pandas as pd
import numpy as np
from numpy import array
import cvxpy as cp
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
import os
import calendar
class TuijianJob(AbstractDPJob):


    def __init__(self,
                 p_tmpl_no=None, p_cog_dest=None):

        super(TuijianJob, self).__init__()
        self.tmpl_no = p_tmpl_no
        self.cog_dest = p_cog_dest
        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):

        super(TuijianJob, self).do_execute()

        tmpl_no = self.tmpl_no
        cog_dest = self.cog_dest
        year_str = tmpl_no[0:4]
        month_str = tmpl_no[4:6]
        year_tmp = int(year_str)
        month_tmp = int(month_str)
        days_tmp = calendar.monthrange(year_tmp, month_tmp)[1]
        # 数据库配置写死
        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'g0mazzai'
        DB_PASSWORD_MPP_DB2 = 'g0mazzaibg00'

        # 数据库连接函数写死
        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
                                       DB_PORT_MPP_DB2,
                                       DB_DBNAME_MPP_DB2,
                                       DB_USER_MPP_DB2,
                                       DB_PASSWORD_MPP_DB2)
        #读数据
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_KIND_INFO " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_meizhong = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhong.columns = data_meizhong.columns.str.upper()
        sql = " select SCHEME_NAME,COKING_PCC_RATIO,COKING_FATCOAL_RATIO,COKING_GFCOAL_RATIO, " \
              " COKING_GASCOAL_RATIO,COKING_LEANCOAL_RATIO,UNIT_PRICE,ASH,COKE_VM,S, "\
              " COKING_COKEYR,COKE_OUTPUT,COG_GEN " \
              " from BG00MAZZAI.T_ADS_FACT_YLMX_COKE_MODEL_S " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_jieguotongji = pd.read_sql_query(sql, con=db_conn_mpp)
        data_jieguotongji.columns = data_jieguotongji.columns.str.upper()
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,ASH,COKE_VM,S " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_IND_INFO " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_meizhi = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhi.columns = data_meizhi.columns.str.upper()
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,ACT_CONSUME,UNIT_PRICE " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_sourceprice = pd.read_sql_query(sql, con=db_conn_mpp)
        data_sourceprice.columns = data_sourceprice.columns.str.upper()
        sql = " select COKEYR_CONST,COG_GEN_CONST,SINTER_CRSCOKE_RATIO, " \
              " COKING_METCOKRAT,PCOKE_COKEPOWD_RATE,ELECOUT,COAL_USE, " \
              " COKE_OUTPUT,POWOUTSRC_PRICE,NG_PRICE,THMCOAL_PRICE " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_COEF " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_canshu = pd.read_sql_query(sql, con=db_conn_mpp)
        data_canshu.columns = data_canshu.columns.str.upper()
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,MIN_VALUE,MAX_VALUE,FLAG " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO " \
              " where TMPL_NO ='%s' and DATA_TYPE='TJ' and FLAG='性状' " % (tmpl_no)
        data_bili = pd.read_sql_query(sql, con=db_conn_mpp)
        data_bili.columns = data_bili.columns.str.upper()
        data_bili.rename(columns={'MAX_VALUE': 'UL'}, inplace=True)
        data_bili.rename(columns={'MIN_VALUE': 'LL'}, inplace=True)
        data_bili.UL.fillna(100, inplace=True)
        data_bili.LL.fillna(0, inplace=True)
        var_list = ['主焦', '肥煤', '1/3焦', '气煤', '瘦煤']
        data_bili_1 = data_bili[(data_bili['VAR'] == '主焦')]
        data_bili_1 = data_bili_1.reset_index(drop=True)
        ul_var_1 = data_bili_1.loc[0]['UL']
        ll_var_1 = data_bili_1.loc[0]['LL']
        data_bili_2 = data_bili[(data_bili['VAR'] == '肥煤')]
        data_bili_2 = data_bili_2.reset_index(drop=True)
        ul_var_2 = data_bili_2.loc[0]['UL']
        ll_var_2 = data_bili_2.loc[0]['LL']
        data_bili_3 = data_bili[(data_bili['VAR'] == '1/3焦')]
        data_bili_3 = data_bili_3.reset_index(drop=True)
        ul_var_3 = data_bili_3.loc[0]['UL']
        ll_var_3 = data_bili_3.loc[0]['LL']
        data_bili_4 = data_bili[(data_bili['VAR'] == '气煤')]
        data_bili_4 = data_bili_4.reset_index(drop=True)
        ul_var_4 = data_bili_4.loc[0]['UL']
        ll_var_4 = data_bili_4.loc[0]['LL']
        data_bili_5 = data_bili[(data_bili['VAR'] == '瘦煤')]
        data_bili_5 = data_bili_5.reset_index(drop=True)
        ul_var_5 = data_bili_5.loc[0]['UL']
        ll_var_5 = data_bili_5.loc[0]['LL']

        chengjiaolv_constant = data_canshu.loc[0]['COKEYR_CONST']
        cogfasheng_constant = data_canshu.loc[0]['COG_GEN_CONST']
        cujiaolv = data_canshu.loc[0]['SINTER_CRSCOKE_RATIO']
        yejinjiaolv = data_canshu.loc[0]['COKING_METCOKRAT']
        waigoujiaofenlv = data_canshu.loc[0]['PCOKE_COKEPOWD_RATE']
        cogfadian = data_canshu.loc[0]['ELECOUT']
        COGfadian = cogfadian
        fadianmeihao = data_canshu.loc[0]['COAL_USE']
        jiaotanchanliang = data_canshu.loc[0]['COKE_OUTPUT']
        price_waigoudian = data_canshu.loc[0]['POWOUTSRC_PRICE']
        price_tianranqi = data_canshu.loc[0]['NG_PRICE']
        price_fadianmei = data_canshu.loc[0]['THMCOAL_PRICE']
        data_other1 = data_sourceprice[data_sourceprice['PROD_DSCR'] == '外购焦炭']
        data_other1 = data_other1.reset_index(drop=True)
        price_waigoujiaotan = data_other1.loc[0]['UNIT_PRICE']
        data_other2 = data_sourceprice[data_sourceprice['PROD_DSCR'] == '烧结燃料']
        data_other2 = data_other2.reset_index(drop=True)
        price_shaojieranliao = data_other2.loc[0]['UNIT_PRICE']
        # data_other3 = data_sourceprice[data_sourceprice['PROD_DSCR'] == 'COG']
        # data_other3 = data_other3.reset_index(drop=True)
        # price_cog = data_other3.loc[0]['UNIT_PRICE']
        price_cog = 0
        data_sourceprice = data_sourceprice[data_sourceprice['VAR'] != '其他参数']
        data_sourceprice = data_sourceprice.reset_index(drop=True)

        data_month = data_jieguotongji[data_jieguotongji['SCHEME_NAME'] == '月预算']
        data_month = data_month.reset_index(drop=True)
        month_var1 = data_month.loc[0]['COKING_PCC_RATIO']
        month_var2 = data_month.loc[0]['COKING_FATCOAL_RATIO']
        month_var3 = data_month.loc[0]['COKING_GFCOAL_RATIO']
        month_var4 = data_month.loc[0]['COKING_GASCOAL_RATIO']
        month_var5 = data_month.loc[0]['COKING_LEANCOAL_RATIO']


        data_meizhi = data_meizhi[['PROD_CODE', 'ASH', 'COKE_VM', 'S']]
        data_sourceprice = data_sourceprice[['PROD_CODE', 'ACT_CONSUME', 'UNIT_PRICE']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_meizhong, data_sourceprice, on=v, how='left')
        v = ['PROD_CODE']
        df4 = pd.merge(df3, data_meizhi, on=v, how='left')
        df4['TOTAL_PRICE'] = df4['ACT_CONSUME'] * df4['UNIT_PRICE']
        df4['TOTAL_ASH'] = df4['ACT_CONSUME'] * df4['ASH']
        df4['TOTAL_COKE_VM'] = df4['ACT_CONSUME'] * df4['COKE_VM']
        df4['TOTAL_S'] = df4['ACT_CONSUME'] * df4['S']

        data_var1 = df4[df4['VAR'] == '主焦']
        data_var1 = data_var1.reset_index(drop=True)
        var1_sum_wt = data_var1['ACT_CONSUME'].sum()
        var1_sum_price = data_var1['TOTAL_PRICE'].sum()
        var1_sum_ash = data_var1['TOTAL_ASH'].sum()
        var1_sum_coke_vm = data_var1['TOTAL_COKE_VM'].sum()
        var1_sum_s = data_var1['TOTAL_S'].sum()
        var1_unit_price = var1_sum_price / var1_sum_wt
        var1_ash = var1_sum_ash / var1_sum_wt
        var1_coke_vm = var1_sum_coke_vm / var1_sum_wt
        var1_s = var1_sum_s / var1_sum_wt
        data_var2 = df4[df4['VAR'] == '肥煤']
        data_var2 = data_var2.reset_index(drop=True)
        var2_sum_wt = data_var2['ACT_CONSUME'].sum()
        var2_sum_price = data_var2['TOTAL_PRICE'].sum()
        var2_sum_ash = data_var2['TOTAL_ASH'].sum()
        var2_sum_coke_vm = data_var2['TOTAL_COKE_VM'].sum()
        var2_sum_s = data_var2['TOTAL_S'].sum()
        var2_unit_price = var2_sum_price / var2_sum_wt
        var2_ash = var2_sum_ash / var2_sum_wt
        var2_coke_vm = var2_sum_coke_vm / var2_sum_wt
        var2_s = var2_sum_s / var2_sum_wt
        data_var3 = df4[df4['VAR'] == '1/3焦']
        data_var3 = data_var3.reset_index(drop=True)
        var3_sum_wt = data_var3['ACT_CONSUME'].sum()
        var3_sum_price = data_var3['TOTAL_PRICE'].sum()
        var3_sum_ash = data_var3['TOTAL_ASH'].sum()
        var3_sum_coke_vm = data_var3['TOTAL_COKE_VM'].sum()
        var3_sum_s = data_var3['TOTAL_S'].sum()
        var3_unit_price = var3_sum_price / var3_sum_wt
        var3_ash = var3_sum_ash / var3_sum_wt
        var3_coke_vm = var3_sum_coke_vm / var3_sum_wt
        var3_s = var3_sum_s / var3_sum_wt
        data_var4 = df4[df4['VAR'] == '气煤']
        data_var4 = data_var4.reset_index(drop=True)
        var4_sum_wt = data_var4['ACT_CONSUME'].sum()
        var4_sum_price = data_var4['TOTAL_PRICE'].sum()
        var4_sum_ash = data_var4['TOTAL_ASH'].sum()
        var4_sum_coke_vm = data_var4['TOTAL_COKE_VM'].sum()
        var4_sum_s = data_var4['TOTAL_S'].sum()
        var4_unit_price = var4_sum_price / var4_sum_wt
        var4_ash = var4_sum_ash / var4_sum_wt
        var4_coke_vm = var4_sum_coke_vm / var4_sum_wt
        var4_s = var4_sum_s / var4_sum_wt
        data_var5 = df4[df4['VAR'] == '瘦煤']
        data_var5 = data_var5.reset_index(drop=True)
        var5_sum_wt = data_var5['ACT_CONSUME'].sum()
        var5_sum_price = data_var5['TOTAL_PRICE'].sum()
        var5_sum_ash = data_var5['TOTAL_ASH'].sum()
        var5_sum_coke_vm = data_var5['TOTAL_COKE_VM'].sum()
        var5_sum_s = data_var5['TOTAL_S'].sum()
        var5_unit_price = var5_sum_price / var5_sum_wt
        var5_ash = var5_sum_ash / var5_sum_wt
        var5_coke_vm = var5_sum_coke_vm / var5_sum_wt
        var5_s = var5_sum_s / var5_sum_wt

        month_unit_price = month_var1 / 100 * var1_unit_price + month_var2 / 100 * var2_unit_price + month_var3 / 100 * var3_unit_price + month_var4 / 100 * var4_unit_price + month_var5 / 100 * var5_unit_price
        month_ash = month_var1 / 100 * var1_ash + month_var2 / 100 * var2_ash + month_var3 / 100 * var3_ash + month_var4 / 100 * var4_ash + month_var5 / 100 * var5_ash
        month_coke_vm = month_var1 / 100 * var1_coke_vm + month_var2 / 100 * var2_coke_vm + month_var3 / 100 * var3_coke_vm + month_var4 / 100 * var4_coke_vm + month_var5 / 100 * var5_coke_vm
        month_s = month_var1 / 100 * var1_s + month_var2 / 100 * var2_s + month_var3 / 100 * var3_s + month_var4 / 100 * var4_s + month_var5 / 100 * var5_s

        month_chengjiaolv = 97 - month_coke_vm * 5 / 6 + chengjiaolv_constant

        ganmeiliang = jiaotanchanliang / month_chengjiaolv * 100
        month_jiaotanchanliang = jiaotanchanliang
        month_cogfasheng = (9.37 * month_coke_vm + 66.7) * ganmeiliang / days_tmp / 24 + cogfasheng_constant
        import numpy as np
        from numpy import array
        price_array = array([var1_unit_price, var2_unit_price, var3_unit_price, var4_unit_price, var5_unit_price])
        # ash_array = array([var1_ash, var2_ash, var3_ash, var4_ash, var5_ash])
        coke_vm_array = array([var1_coke_vm, var2_coke_vm, var3_coke_vm, var4_coke_vm, var5_coke_vm])
        # s_array = array([var1_s, var2_s, var3_s, var4_s, var5_s])

        # 天然气
        def cal_total_cost_fadian1(x):
            adjust_unit_price = price_array @ x
            adjust_unit_price = adjust_unit_price / 100
            adjust_coke_vm = coke_vm_array @ x
            adjust_coke_vm = adjust_coke_vm / 100
            adjust_chengjiaolv = 97 - adjust_coke_vm * 5 / 6 + chengjiaolv_constant
            adjust_jiaotanchanliang = ganmeiliang * adjust_chengjiaolv / 100
            adjust_cogfasheng = (9.37 * adjust_coke_vm + 66.7) * ganmeiliang / days_tmp / 24 + cogfasheng_constant
            delta_jiaotanchanliang = adjust_jiaotanchanliang - month_jiaotanchanliang
            delta_culiaoliang = delta_jiaotanchanliang * cujiaolv
            delta_waigoujiao = delta_jiaotanchanliang * yejinjiaolv / (1 - waigoujiaofenlv)
            delta_cujiaocaigou = delta_waigoujiao * waigoujiaofenlv - delta_culiaoliang
            delta_waigoujiao_cost = -delta_waigoujiao * price_waigoujiaotan
            delta_cujiaocaigou_cost = delta_cujiaocaigou * price_shaojieranliao
            delta_peihemei_cost = (adjust_unit_price - month_unit_price) * ganmeiliang
            delta_COGfashengliang = (month_cogfasheng - adjust_cogfasheng) * days_tmp * 24
            delta_COG_fadian = delta_COGfashengliang * COGfadian
            tianranqifadian = delta_COGfashengliang / 2
            fadianmeifadian = delta_COG_fadian * fadianmeihao / 100
            waigoudian = delta_COG_fadian
            delta_COG_cost1 = tianranqifadian * price_tianranqi
            delta_COG_cost2 = fadianmeifadian * price_fadianmei / 10000
            delta_COG_cost3 = waigoudian * price_waigoudian
            total_cost1 = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost1
            total_cost2 = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost2
            total_cost3 = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost3
            return total_cost1

        def cal_total_cost_fadian2(x):
            adjust_unit_price = price_array @ x
            adjust_unit_price = adjust_unit_price / 100
            adjust_coke_vm = coke_vm_array @ x
            adjust_coke_vm = adjust_coke_vm / 100
            adjust_chengjiaolv = 97 - adjust_coke_vm * 5 / 6 + chengjiaolv_constant
            adjust_jiaotanchanliang = ganmeiliang * adjust_chengjiaolv / 100
            adjust_cogfasheng = (9.37 * adjust_coke_vm + 66.7) * ganmeiliang / days_tmp / 24 + cogfasheng_constant
            delta_jiaotanchanliang = adjust_jiaotanchanliang - month_jiaotanchanliang
            delta_culiaoliang = delta_jiaotanchanliang * cujiaolv
            delta_waigoujiao = delta_jiaotanchanliang * yejinjiaolv / (1 - waigoujiaofenlv)
            delta_cujiaocaigou = delta_waigoujiao * waigoujiaofenlv - delta_culiaoliang
            delta_waigoujiao_cost = -delta_waigoujiao * price_waigoujiaotan
            delta_cujiaocaigou_cost = delta_cujiaocaigou * price_shaojieranliao
            delta_peihemei_cost = (adjust_unit_price - month_unit_price) * ganmeiliang
            delta_COGfashengliang = (month_cogfasheng - adjust_cogfasheng) * days_tmp * 24
            delta_COG_fadian = delta_COGfashengliang * COGfadian
            tianranqifadian = delta_COGfashengliang / 2
            fadianmeifadian = delta_COG_fadian * fadianmeihao / 100
            waigoudian = delta_COG_fadian
            delta_COG_cost1 = tianranqifadian * price_tianranqi
            delta_COG_cost2 = fadianmeifadian * price_fadianmei / 10000
            delta_COG_cost3 = waigoudian * price_waigoudian
            total_cost1 = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost1
            total_cost2 = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost2
            total_cost3 = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost3
            return total_cost2

        def cal_total_cost_fadian3(x):
            adjust_unit_price = price_array @ x
            adjust_unit_price = adjust_unit_price / 100
            adjust_coke_vm = coke_vm_array @ x
            adjust_coke_vm = adjust_coke_vm / 100
            adjust_chengjiaolv = 97 - adjust_coke_vm * 5 / 6 + chengjiaolv_constant
            adjust_jiaotanchanliang = ganmeiliang * adjust_chengjiaolv / 100
            adjust_cogfasheng = (9.37 * adjust_coke_vm + 66.7) * ganmeiliang / days_tmp / 24 + cogfasheng_constant
            delta_jiaotanchanliang = adjust_jiaotanchanliang - month_jiaotanchanliang
            delta_culiaoliang = delta_jiaotanchanliang * cujiaolv
            delta_waigoujiao = delta_jiaotanchanliang * yejinjiaolv / (1 - waigoujiaofenlv)
            delta_cujiaocaigou = delta_waigoujiao * waigoujiaofenlv - delta_culiaoliang
            delta_waigoujiao_cost = -delta_waigoujiao * price_waigoujiaotan
            delta_cujiaocaigou_cost = delta_cujiaocaigou * price_shaojieranliao
            delta_peihemei_cost = (adjust_unit_price - month_unit_price) * ganmeiliang
            delta_COGfashengliang = (month_cogfasheng - adjust_cogfasheng) * days_tmp * 24
            delta_COG_fadian = delta_COGfashengliang * COGfadian
            tianranqifadian = delta_COGfashengliang / 2
            fadianmeifadian = delta_COG_fadian * fadianmeihao / 100
            waigoudian = delta_COG_fadian
            delta_COG_cost1 = tianranqifadian * price_tianranqi
            delta_COG_cost2 = fadianmeifadian * price_fadianmei / 10000
            delta_COG_cost3 = waigoudian * price_waigoudian
            total_cost1 = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost1
            total_cost2 = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost2
            total_cost3 = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost3
            return total_cost3


        e1 = array([[1, 0, 0, 0, 0],
                   [-1, 0, 0, 0, 0],
                   [0, 1, 0, 0, 0],
                   [0, -1, 0, 0, 0],
                   [0, 0, 1, 0, 0],
                   [0, 0, -1, 0, 0],
                   [0, 0, 0, 1, 0],
                   [0, 0, 0, -1, 0],
                   [0, 0, 0, 0, 1],
                   [0, 0, 0, 0, -1]])
        f1 = array([ul_var_1, -ll_var_1, ul_var_2, -ll_var_2, ul_var_3, -ll_var_3, ul_var_4, -ll_var_4, ul_var_5, -ll_var_5])
        e2 = array([1, 1, 1, 1, 1])
        f2 = array([100])

        x1 = cp.Variable(5)
        obj = cp.Minimize(cal_total_cost_fadian1(x1))
        cons = [e1 @ x1 <= f1, e2 @ x1 == f2, x1 >= 0]
        prob1 = cp.Problem(obj, cons)
        prob1.solve(solver='GLPK_MI', verbose=True)
        print("最优初始值为:", prob1.value)
        print("最优初始解为：\n", x1.value)
        success = 0
        if x1.value is None:
            success = 0
            message = '求不出最优方案'
            return message
        x2 = cp.Variable(5)
        obj = cp.Minimize(cal_total_cost_fadian2(x2))
        cons = [e1 @ x2 <= f1, e2 @ x2 == f2, x2 >= 0]
        prob2 = cp.Problem(obj, cons)
        prob2.solve(solver='GLPK_MI', verbose=True)
        print("最优初始值为:", prob2.value)
        print("最优初始解为：\n", x2.value)
        success = 0
        if x2.value is None:
            success = 0
            message = '求不出最优方案'
            return message
        x3 = cp.Variable(5)
        obj = cp.Minimize(cal_total_cost_fadian3(x3))
        cons = [e1 @ x3 <= f1, e2 @ x3 == f2, x3 >= 0]
        prob3 = cp.Problem(obj, cons)
        prob3.solve(solver='GLPK_MI', verbose=True)
        print("最优初始值为:", prob3.value)
        print("最优初始解为：\n", x3.value)
        success = 0
        if x3.value is None:
            success = 0
            message = '求不出最优方案'
            return message
        if prob1.value >= prob2.value and prob1.value >= prob3.value:
            x_value_tmp = x1.value
            chushi_z = cal_total_cost_fadian1(abs(x_value_tmp))
        elif prob2.value >= prob1.value and prob2.value >= prob3.value:
            x_value_tmp = x2.value
            chushi_z = cal_total_cost_fadian2(abs(x_value_tmp))
        elif prob3.value >= prob1.value and prob3.value >= prob2.value:
            x_value_tmp = x3.value
            chushi_z = cal_total_cost_fadian3(abs(x_value_tmp))
        adjust_var1 = abs(x_value_tmp[0])
        adjust_var2 = abs(x_value_tmp[1])
        adjust_var3 = abs(x_value_tmp[2])
        adjust_var4 = abs(x_value_tmp[3])
        adjust_var5 = abs(x_value_tmp[4])

        data_jieguotongji_out = pd.DataFrame(
            columns=['SCHEME_NAME', 'COKING_PCC_RATIO', 'COKING_FATCOAL_RATIO', 'COKING_GFCOAL_RATIO',
                     'COKING_GASCOAL_RATIO', 'COKING_LEANCOAL_RATIO'])

        dict = {}
        dict['SCHEME_NAME'] = '推荐调整'
        dict['COKING_PCC_RATIO'] = adjust_var1
        dict['COKING_FATCOAL_RATIO'] = adjust_var2
        dict['COKING_GFCOAL_RATIO'] = adjust_var3
        dict['COKING_GASCOAL_RATIO'] = adjust_var4
        dict['COKING_LEANCOAL_RATIO'] = adjust_var5
        new_row = pd.Series(dict)
        data_jieguotongji_out = data_jieguotongji_out.append(new_row, ignore_index=True)


        data_jieguotongji_out['TMPL_NO'] = tmpl_no
        data_jieguotongji_out['DATA_TYPE'] = 'TJ'


        now = datetime.datetime.now()
        now_1 = now.strftime('%Y%m%d%H%M%S')
        data_jieguotongji_out['REC_CREATE_TIME'] = now_1
        data_jieguotongji_out['REC_CREATOR'] = 'zzai'
        data_jieguotongji_out['REC_REVISE_TIME'] = now_1
        data_jieguotongji_out['REC_REVISOR'] = 'zzai'
        data_jieguotongji_out_rounded = data_jieguotongji_out.round(3)

        sql = " DELETE FROM " \
              " BG00MAZZAI.T_ADS_FACT_YLMX_COKE_MODEL_S" \
              " WHERE 1=1 AND TMPL_NO ='%s' and DATA_TYPE='TJ' " % (tmpl_no)
        db_conn_mpp.execute(sql)

        #####存入到数据库
        data_jieguotongji_out_rounded.to_sql(name='T_ADS_FACT_YLMX_COKE_MODEL_S'.lower(),
                               con=db_conn_mpp,
                               schema='BG00MAZZAI'.lower(),
                               index=False,
                               if_exists='append',
                               chunksize=10000)
        # writer = pd.ExcelWriter('炼焦结果统计表测算.xlsx')
        # data_jieguotongji_out_rounded.to_excel(writer, sheet_name='Sheet1', index=False)
        # writer.save()
        #
        message = '计算结果入库成功'
        # print('finish')
        return message























